import datetime

from PyQt5 import QtCore
from PyQt5.QtCore import QRegExp, QTimer, QDateTime, Qt, QDate
from PyQt5.QtGui import QRegExpValidator
from src.gui import main_ui, warehouse_ui, check_out_dialog, sale_statistics_dialog, purchase_history_dialog, \
    on_off_shelve_history_dialog, sale_history_dialog, manage_history_dialog, purchase_dialog, shelves_ui, employee_ui, \
    goods_ui, on_shelves_dialog, off_shelves_dialog, goods_about_to_expire_dialog
from src.exceptions import sales_exception, warehouse_exception, employee_exception, common_exception, goods_exception, \
    shelves_exception
import sys
from PyQt5.QtWidgets import QApplication, QMainWindow, QTableWidgetItem, QMessageBox, QAbstractItemView, QDialog, \
    QFormLayout, QLabel, QDialogButtonBox, QDateTimeEdit, QTableWidget, QDateEdit, QListWidgetItem, QInputDialog, \
    QItemDelegate, QWidget

# 利用一个控制器来控制页面的跳转
from src.sql import utils, initialization

# 全局常量
# 日期格式
datetime_format = 'yyyy-MM-dd hh:mm:ss'
datetime_format_extra = 'yyyy-MM-dd hh:mm:ss dddd'
date_format = 'yyyy-MM-dd'
# 正则表达式
int_reg = '[0-9]+$'
id_reg = '\\w{{{}}}$'
pos_float_reg = '^(([0-9]+\\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\\.[0-9]+)|([0-9]*[1-9][0-9]*))$'
phone_number_reg = '[0-9]{11}$'


class Controller:
    def __init__(self):
        self.SupermarketShow = SupermarketShow()
        self.WarehouseShow = WarehouseShow()
        self.ShelvesShow = ShelvesShow()
        self.EmployeeShow = EmployeeShow()
        self.GoodsShow = GoodsShow()

    def show_supermarket(self):
        # 绑定跳转按钮
        # 主界面
        self.SupermarketShow.goto_warehouse.clicked.connect(
            lambda: self.x_to_y(x=self.SupermarketShow, y=self.WarehouseShow))
        self.SupermarketShow.goto_shelves.clicked.connect(
            lambda: self.x_to_y(x=self.SupermarketShow, y=self.ShelvesShow))
        self.SupermarketShow.goto_employee.clicked.connect(
            lambda: self.x_to_y(x=self.SupermarketShow, y=self.EmployeeShow))
        self.SupermarketShow.goto_goods.clicked.connect(
            lambda: self.x_to_y(x=self.SupermarketShow, y=self.GoodsShow))
        # 仓库界面
        self.WarehouseShow.goto_main.clicked.connect(lambda: self.x_to_y(x=self.WarehouseShow, y=self.SupermarketShow))
        self.WarehouseShow.goto_shelves.clicked.connect(lambda: self.x_to_y(x=self.WarehouseShow, y=self.ShelvesShow))
        self.WarehouseShow.goto_employee.clicked.connect(lambda: self.x_to_y(x=self.WarehouseShow, y=self.EmployeeShow))
        self.WarehouseShow.goto_goods.clicked.connect(lambda: self.x_to_y(x=self.WarehouseShow, y=self.GoodsShow))
        # 货架界面
        self.ShelvesShow.goto_main.clicked.connect(lambda: self.x_to_y(x=self.ShelvesShow, y=self.SupermarketShow))
        self.ShelvesShow.goto_warehouse.clicked.connect(lambda: self.x_to_y(x=self.ShelvesShow, y=self.WarehouseShow))
        self.ShelvesShow.goto_employee.clicked.connect(lambda: self.x_to_y(x=self.ShelvesShow, y=self.EmployeeShow))
        self.ShelvesShow.goto_goods.clicked.connect(lambda: self.x_to_y(x=self.ShelvesShow, y=self.GoodsShow))
        # 员工界面
        self.EmployeeShow.goto_main.clicked.connect(lambda: self.x_to_y(x=self.EmployeeShow, y=self.SupermarketShow))
        self.EmployeeShow.goto_warehouse.clicked.connect(lambda: self.x_to_y(x=self.EmployeeShow, y=self.WarehouseShow))
        self.EmployeeShow.goto_shelves.clicked.connect(lambda: self.x_to_y(x=self.EmployeeShow, y=self.ShelvesShow))
        self.EmployeeShow.goto_goods.clicked.connect(lambda: self.x_to_y(x=self.EmployeeShow, y=self.GoodsShow))
        # 商品界面
        self.GoodsShow.goto_main.clicked.connect(lambda: self.x_to_y(x=self.GoodsShow, y=self.SupermarketShow))
        self.GoodsShow.goto_warehouse.clicked.connect(lambda: self.x_to_y(x=self.GoodsShow, y=self.WarehouseShow))
        self.GoodsShow.goto_shelves.clicked.connect(lambda: self.x_to_y(x=self.GoodsShow, y=self.ShelvesShow))
        self.GoodsShow.goto_employee.clicked.connect(lambda: self.x_to_y(x=self.GoodsShow, y=self.EmployeeShow))
        # 默认展示主界面
        self.SupermarketShow.show()

    def x_to_y(self, x, y):
        """
        从x页面跳转到y页面

        :param x: 源页面
        :param y: 目标页面
        :return:
        """
        x.close()
        y.show()
        if y != self.SupermarketShow:
            y.tableWidget_start()


class goods_about_to_expire_Dialog(QMainWindow, goods_about_to_expire_dialog.Ui_goods_about_to_expire):
    def __init__(self, parent=None):
        super(goods_about_to_expire_Dialog, self).__init__(parent)
        self.setupUi(self)


class off_shelves_Dialog(QMainWindow, off_shelves_dialog.Ui_off_shelves):
    def __init__(self, parent=None):
        super(off_shelves_Dialog, self).__init__(parent)
        self.setupUi(self)


class on_shelves_Dialog(QMainWindow, on_shelves_dialog.Ui_on_shelves):
    def __init__(self, parent=None):
        super(on_shelves_Dialog, self).__init__(parent)
        self.setupUi(self)


class purchase_Dialog(QMainWindow, purchase_dialog.Ui_purchase):
    def __init__(self, parent=None):
        super(purchase_Dialog, self).__init__(parent)
        self.setupUi(self)


class manage_history_Dialog(QMainWindow, manage_history_dialog.Ui_manage_history_dialog):
    def __init__(self, parent=None):
        super(manage_history_Dialog, self).__init__(parent)
        self.setupUi(self)


class sale_history_Dialog(QMainWindow, sale_history_dialog.Ui_sale_history_dialog):
    def __init__(self, parent=None):
        super(sale_history_Dialog, self).__init__(parent)
        self.setupUi(self)


class on_off_shelve_history_Dialog(QMainWindow, on_off_shelve_history_dialog.Ui_on_off_shelve_history_dialog):
    def __init__(self, parent=None):
        super(on_off_shelve_history_Dialog, self).__init__(parent)
        self.setupUi(self)


class purchase_history_Dialog(QMainWindow, purchase_history_dialog.Ui_purchase_history_dialog):
    def __init__(self, parent=None):
        super(purchase_history_Dialog, self).__init__(parent)
        self.setupUi(self)


class sale_statistics_Dialog(QMainWindow, sale_statistics_dialog.Ui_sale_statistics_dialog):
    def __init__(self, parent=None):
        super(sale_statistics_Dialog, self).__init__(parent)
        self.setupUi(self)


class check_out_Dialog(QMainWindow, check_out_dialog.Ui_check_out_dialog):
    def __init__(self, parent=None):
        super(check_out_Dialog, self).__init__(parent)
        self.setupUi(self)


class EmptyDelegate(QItemDelegate):
    """
    设置列不可编辑

    """

    def __init__(self, parent):
        super(EmptyDelegate, self).__init__(parent)

    def createEditor(self, parent: QWidget, option: 'QStyleOptionViewItem', index: QtCore.QModelIndex) -> QWidget:
        return None


class GoodsShow(QMainWindow, goods_ui.Ui_goods):
    def __init__(self, parent=None):
        super(GoodsShow, self).__init__(parent)
        self.setupUi(self)
        # 初始化&属性
        self.db, self.cursor = db, cursor
        self.errorBox = QMessageBox()
        self.errorBox.setIcon(QMessageBox.Critical)
        self.warningBox = QMessageBox()
        self.warningBox.setIcon(QMessageBox.Warning)
        self.rightBox = QMessageBox()
        self.rightBox.setIcon(QMessageBox.Information)
        self.max_num_records = 50
        self.selected_Gid = None
        # 上架
        self.on_shelves_Dialog = on_shelves_Dialog()
        self.on_shelves_Dialog.warehouse_tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不可修改
        self.on_shelves_Dialog.warehouse_tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)  # 选中一行
        self.on_shelves_Dialog.warehouse_tableWidget.setSelectionMode(
            QAbstractItemView.SingleSelection)  # 设置为不可以选中多个目标
        self.on_shelves_Dialog.Sid_lineEdit.setValidator(QRegExpValidator(QRegExp(id_reg.format(4))))
        self.on_shelves_Dialog.change_num_lineEdit.setValidator(QRegExpValidator(QRegExp(int_reg)))
        self.on_shelves_Dialog.on_shelve_pushButton.clicked.connect(self.on_shelves)

        # 下架
        self.off_shelves_Dialog = off_shelves_Dialog()
        self.off_shelves_Dialog.shleves_tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不可修改
        self.off_shelves_Dialog.shleves_tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)  # 选中一行
        self.off_shelves_Dialog.shleves_tableWidget.setSelectionMode(
            QAbstractItemView.SingleSelection)  # 设置为不可以选中多个目标
        self.off_shelves_Dialog.Wid_lineEdit.setValidator(QRegExpValidator(QRegExp(id_reg.format(4))))
        self.off_shelves_Dialog.change_num_lineEdit.setValidator(QRegExpValidator(QRegExp(int_reg)))
        self.off_shelves_Dialog.off_shelve_pushButton.clicked.connect(self.off_shelves)

        # 即将到期界面
        self.goods_about_to_expire_Dialog = goods_about_to_expire_Dialog()
        self.goods_about_to_expire_Dialog.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不可修改
        self.goods_about_to_expire_Dialog.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)  # 选中一行
        self.goods_about_to_expire_Dialog.tableWidget.setSelectionMode(QAbstractItemView.SingleSelection)  # 设置为可以选中多个目标
        self.goods_about_to_expire_Dialog.tableWidget.horizontalHeader().sectionClicked.connect(
            self.tableWidget.selectAll)  # 点击表头为全选

        # 表格
        self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不可修改
        self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)  # 选中一行
        self.tableWidget.setSelectionMode(QAbstractItemView.SingleSelection)  # 设置为可以选中多个目标
        self.tableWidget.horizontalHeader().sectionClicked.connect(self.tableWidget.selectAll)  # 点击表头为全选

        # 搜索框
        self.searchButton.clicked.connect(self.search_goods_by_name_Button)

        # 上下架
        self.on_shelves_pushButton.clicked.connect(self.on_shelves_start)
        self.off_shelves_pushButton.clicked.connect(self.off_shelves_start)

        # 修改售价
        self.Sprice_change_pushButton.clicked.connect(self.price_change)

        # 查询即将到期商品
        self.Quality_pushButton.clicked.connect(self.search_goods_about_to_expire)

    def price_change(self):
        """
        修改商品售价

        :return:
        """
        if self.tableWidget.currentRow() == -1:
            self.errorBox.setText('请先选中一个商品，再点击修改商品售价！')
            self.errorBox.exec_()
            return
        changed_price_inputDialog = QInputDialog()
        changed_price, status = changed_price_inputDialog.getDouble(self, '修改商品售价', '新的售价')
        if not status:
            return
        if changed_price < 0:
            self.errorBox.setText('新的售价不能为负！')
            self.errorBox.exec_()
            return
        changed_value = []
        for i in range(7):
            val = self.tableWidget.item(self.tableWidget.currentRow(), i).text()
            if i == 3 or i == 5:
                val = int(val)
            changed_value.append(val)
        changed_value.append(changed_price)
        utils.update_star(self.cursor, 'goods', changed_value, changed_value[1])
        self.rightBox.setText('修改商品[{}]售价成功！'.format(changed_value[1]))
        self.rightBox.exec_()
        self.tableWidget_start()

    def search_goods_about_to_expire(self):
        """
        查询即将到期商品

        :return:
        """
        self.goods_about_to_expire_Dialog.tableWidget.clear()
        self.goods_about_to_expire_Dialog.tableWidget.setRowCount(0)
        min_left_quality_days_inputDialog = QInputDialog()
        min_left_quality_days, status = min_left_quality_days_inputDialog.getInt(self, '查询即将到期商品', '请输入需要查询的保质期最大剩余天数')
        if not status:
            return
        if min_left_quality_days < 0:
            self.errorBox.setText('保质期剩余最大天数不能为负！')
            self.errorBox.exec_()
            return
        goods = utils.get_goods_about_to_expire(self.cursor, datetime.datetime.now().strftime("%Y-%m-%d"),
                                                min_left_quality_days)
        for result in goods:
            result = goods[result]
            now_row = self.goods_about_to_expire_Dialog.tableWidget.rowCount()
            self.goods_about_to_expire_Dialog.tableWidget.setRowCount(now_row + 1)
            col = 0
            names = utils.get_attributes(self.cursor, 'goods')
            names.append('expiration_date')
            for i in range(9):
                self.goods_about_to_expire_Dialog.tableWidget.setItem(now_row, col,
                                                                      QTableWidgetItem('{}'.format(result[names[i]])))
                col += 1
            # 货架
            try:
                Sid = result['shelves']['Sid']
                left = result['shelves']['left']
                latest_launch_date = result['shelves']['latest_launch_date']
            except KeyError:
                Sid = None
                left = None
                latest_launch_date = None
            self.goods_about_to_expire_Dialog.tableWidget.setItem(now_row, 9, QTableWidgetItem('{}'.format(Sid)))
            self.goods_about_to_expire_Dialog.tableWidget.setItem(now_row, 10, QTableWidgetItem('{}'.format(left)))
            self.goods_about_to_expire_Dialog.tableWidget.setItem(now_row, 11,
                                                                  QTableWidgetItem('{}'.format(latest_launch_date)))
            # 仓库
            if len(result['warehouses']) == 2:
                print(len(result['warehouses']))
            self.goods_about_to_expire_Dialog.tableWidget.setColumnCount(
                max(self.goods_about_to_expire_Dialog.tableWidget.columnCount(), 12 + len(result['warehouses']) * 2))
            names = ['商品名', '商品号', '生产日期', '保质期', '生产商', '进货时总数目', '单位', '单位售价', '到期日期', '所在货架号', '货架上剩余数', '最后上架时间']
            for i in range(len(result['warehouses'])):
                names.append('所在第{}个仓库号'.format(i + 1))
                names.append('第{}个仓库剩余数'.format(i + 1))
            self.goods_about_to_expire_Dialog.tableWidget.setHorizontalHeaderLabels(names)
            cur_col = 12
            for warehouses in result['warehouses']:
                self.goods_about_to_expire_Dialog.tableWidget.setItem(now_row, cur_col,
                                                                      QTableWidgetItem('{}'.format(
                                                                          result['warehouses'][warehouses]['Wid'])))
                self.goods_about_to_expire_Dialog.tableWidget.setItem(now_row, cur_col + 1,
                                                                      QTableWidgetItem('{}'.format(
                                                                          result['warehouses'][warehouses]['left'])))
                cur_col += 2
        self.goods_about_to_expire_Dialog.tableWidget.sortItems(8, Qt.DescendingOrder)
        self.goods_about_to_expire_Dialog.show()

    def off_shelves(self):
        """
        下架

        :return:
        """
        Wid = self.off_shelves_Dialog.Wid_lineEdit.text()
        row = self.off_shelves_Dialog.shleves_tableWidget.currentRow()
        if row == -1:
            self.errorBox.setText('请点击需要下架的货架！')
            self.errorBox.exec_()
            return
        Sid = self.off_shelves_Dialog.shleves_tableWidget.item(row, 0).text()
        try:
            change_num = int(self.off_shelves_Dialog.change_num_lineEdit.text())
        except ValueError:
            self.errorBox.setText('下架数目不能为空！')
            self.errorBox.exec_()
            return
        if change_num > int(self.off_shelves_Dialog.shleves_tableWidget.item(row, 1).text()):
            self.errorBox.setText('下架数目不能大于剩余数目！')
            self.errorBox.exec_()
            return
        try:
            utils.launches_off_add(self.cursor, Wid, self.selected_Gid, Sid, None,
                                   datetime.datetime.now().strftime("%Y-%m-%d"), change_num)
        except warehouse_exception.NotFindWid as e:
            self.errorBox.setText('{}'.format(e.value))
            self.errorBox.exec_()
            return
        self.selected_Gid = None
        self.tableWidget_start()
        self.rightBox.setText('下架成功！')
        self.rightBox.exec_()
        self.off_shelves_Dialog.close()

    def off_shelves_start(self):
        """
        下架初始化

        :return:
        """
        self.off_shelves_Dialog.shleves_tableWidget.clearContents()
        self.off_shelves_Dialog.shleves_tableWidget.setRowCount(0)
        if self.tableWidget.currentRow() == -1:
            self.errorBox.setText('请先选中一个商品，再点击上架！')
            self.errorBox.exec_()
            return
        self.selected_Gid = self.tableWidget.item(self.tableWidget.currentRow(), 1).text()
        Sid, num, _ = utils.get_goods_left_shelves(self.cursor, self.selected_Gid)
        now_row = self.off_shelves_Dialog.shleves_tableWidget.rowCount()
        self.off_shelves_Dialog.shleves_tableWidget.setRowCount(now_row + 1)
        self.off_shelves_Dialog.shleves_tableWidget.setItem(now_row, 0, QTableWidgetItem('{}'.format(Sid)))
        self.off_shelves_Dialog.shleves_tableWidget.setItem(now_row, 1, QTableWidgetItem('{}'.format(num)))
        self.off_shelves_Dialog.show()

    def on_shelves(self):
        """
        上架

        :return:
        """
        Sid = self.on_shelves_Dialog.Sid_lineEdit.text()
        row = self.on_shelves_Dialog.warehouse_tableWidget.currentRow()
        if row == -1:
            self.errorBox.setText('请点击需要上架的仓库！')
            self.errorBox.exec_()
            return
        Wid = self.on_shelves_Dialog.warehouse_tableWidget.item(row, 0).text()
        try:
            change_num = int(self.on_shelves_Dialog.change_num_lineEdit.text())
        except ValueError:
            self.errorBox.setText('上架数目不能为空！')
            self.errorBox.exec_()
            return
        if change_num > int(self.on_shelves_Dialog.warehouse_tableWidget.item(row, 1).text()):
            self.errorBox.setText('上架数目不能大于剩余数目！')
            self.errorBox.exec_()
            return
        try:
            utils.launches_off_add(self.cursor, Wid, self.selected_Gid, Sid,
                                   datetime.datetime.now().strftime("%Y-%m-%d"),
                                   None, change_num)
        except shelves_exception.NotFindSid as e:
            self.errorBox.setText('{}'.format(e.value))
            self.errorBox.exec_()
            return
        self.selected_Gid = None
        self.tableWidget_start()
        self.rightBox.setText('上架成功！')
        self.rightBox.exec_()
        self.on_shelves_Dialog.close()

    def on_shelves_start(self):
        """
        上架初始化

        :return:
        """
        self.on_shelves_Dialog.warehouse_tableWidget.clearContents()
        self.on_shelves_Dialog.warehouse_tableWidget.setRowCount(0)
        if self.tableWidget.currentRow() == -1:
            self.errorBox.setText('请先选中一个商品，再点击上架！')
            self.errorBox.exec_()
            return
        self.selected_Gid = self.tableWidget.item(self.tableWidget.currentRow(), 1).text()
        left_shelves = utils.get_goods_left_shelves(self.cursor, self.selected_Gid)
        if left_shelves is not None:
            Sid, _, _ = left_shelves
            self.on_shelves_Dialog.Sid_lineEdit.setText(Sid)
            self.on_shelves_Dialog.Sid_lineEdit.setDisabled(True)
        else:
            self.on_shelves_Dialog.Sid_lineEdit.setDisabled(False)
        for Wid, num in utils.get_goods_left_warehouse_all(self.cursor, self.selected_Gid):
            now_row = self.on_shelves_Dialog.warehouse_tableWidget.rowCount()
            self.on_shelves_Dialog.warehouse_tableWidget.setRowCount(now_row + 1)
            self.on_shelves_Dialog.warehouse_tableWidget.setItem(now_row, 0, QTableWidgetItem('{}'.format(Wid)))
            self.on_shelves_Dialog.warehouse_tableWidget.setItem(now_row, 1, QTableWidgetItem('{}'.format(num)))
        self.on_shelves_Dialog.show()

    def tableWidget_start(self):
        """
        初始化商品表格数据

        :return:
        """
        self.tableWidget.clear()
        self.tableWidget.setRowCount(0)
        goods = utils.goods_get_all(self.cursor, self.max_num_records)
        for result in goods:
            result = goods[result]
            now_row = self.tableWidget.rowCount()
            self.tableWidget.setRowCount(now_row + 1)
            col = 0
            names = utils.get_attributes(self.cursor, 'goods')
            for i in range(8):
                self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(result[names[i]])))
                col += 1
            # 货架
            try:
                Sid = result['shelves']['Sid']
                left = result['shelves']['left']
                latest_launch_date = result['shelves']['latest_launch_date']
            except KeyError:
                Sid = None
                left = None
                latest_launch_date = None
            self.tableWidget.setItem(now_row, 8, QTableWidgetItem('{}'.format(Sid)))
            self.tableWidget.setItem(now_row, 9, QTableWidgetItem('{}'.format(left)))
            self.tableWidget.setItem(now_row, 10, QTableWidgetItem('{}'.format(latest_launch_date)))
            # 仓库
            if len(result['warehouses']) == 2:
                print(len(result['warehouses']))
            self.tableWidget.setColumnCount(max(self.tableWidget.columnCount(), 11 + len(result['warehouses']) * 2))
            names = ['商品名', '商品号', '生产日期', '保质期', '生产商', '进货时总数目', '单位', '单位售价', '所在货架号', '货架上剩余数', '最后上架时间']
            for i in range(len(result['warehouses'])):
                names.append('所在第{}个仓库号'.format(i + 1))
                names.append('第{}个仓库剩余数'.format(i + 1))
            self.tableWidget.setHorizontalHeaderLabels(names)
            cur_col = 11
            for warehouses in result['warehouses']:
                self.tableWidget.setItem(now_row, cur_col,
                                         QTableWidgetItem('{}'.format(result['warehouses'][warehouses]['Wid'])))
                self.tableWidget.setItem(now_row, cur_col + 1,
                                         QTableWidgetItem('{}'.format(result['warehouses'][warehouses]['left'])))
                cur_col += 2

    def search_goods_by_name_Button(self):
        """
        根据商品名称搜索商品

        :return:
        """
        self.tableWidget.clearContents()
        self.tableWidget.setRowCount(0)
        to_search = self.search_lineEdit.text()
        goods = utils.goods_get_by_name(self.cursor, to_search)
        for result in goods:
            result = goods[result]
            now_row = self.tableWidget.rowCount()
            self.tableWidget.setRowCount(now_row + 1)
            col = 0
            names = utils.get_attributes(self.cursor, 'goods')
            for i in range(8):
                self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(result[names[i]])))
                col += 1
            # 货架
            try:
                Sid = result['shelves']['Sid']
                left = result['shelves']['left']
                latest_launch_date = result['shelves']['latest_launch_date']
            except KeyError:
                Sid = None
                left = None
                latest_launch_date = None
            self.tableWidget.setItem(now_row, 8, QTableWidgetItem('{}'.format(Sid)))
            self.tableWidget.setItem(now_row, 9, QTableWidgetItem('{}'.format(left)))
            self.tableWidget.setItem(now_row, 10, QTableWidgetItem('{}'.format(latest_launch_date)))
            # 仓库
            self.tableWidget.setColumnCount(max(self.tableWidget.columnCount(), 11 + len(result['warehouses']) * 2))
            names = ['商品名', '商品号', '生产日期', '保质期', '生产商', '进货时总数目', '单位', '单位售价', '所在货架号', '货架上剩余数', '最后上架时间']
            for i in range(len(result['warehouses'])):
                names.append('所在第{}个仓库号'.format(i + 1))
                names.append('第{}个仓库剩余数'.format(i + 1))
            self.tableWidget.setHorizontalHeaderLabels(names)
            cur_col = 11
            for warehouses in result['warehouses']:
                self.tableWidget.setItem(now_row, cur_col,
                                         QTableWidgetItem('{}'.format(result['warehouses'][warehouses]['Wid'])))
                self.tableWidget.setItem(now_row, cur_col + 1,
                                         QTableWidgetItem('{}'.format(result['warehouses'][warehouses]['left'])))
                cur_col += 2


class EmployeeShow(QMainWindow, employee_ui.Ui_employee):
    def __init__(self, parent=None):
        super(EmployeeShow, self).__init__(parent)
        self.setupUi(self)
        # 初始化&属性
        self.db, self.cursor = db, cursor
        self.errorBox = QMessageBox()
        self.errorBox.setIcon(QMessageBox.Critical)
        self.warningBox = QMessageBox()
        self.warningBox.setIcon(QMessageBox.Warning)
        self.rightBox = QMessageBox()
        self.rightBox.setIcon(QMessageBox.Information)

        # 表格
        self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不可修改
        self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)  # 选中一行
        self.tableWidget.setSelectionMode(QAbstractItemView.SingleSelection)  # 设置为可以选中单个目标
        self.tableWidget.horizontalHeader().sectionClicked.connect(self.tableWidget.selectAll)  # 点击表头为全选
        self.tableWidget.itemChanged.connect(self.update_a_employee)  # 表格内容修改后，数据库也修改

        # 搜索框
        self.searchButton.clicked.connect(self.search_employee_by_name_Button)

        # 修改删除区域
        self.updateButton.clicked.connect(self.update_employee_Button)
        self.confirm_update_Button.clicked.connect(
            lambda: self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers))
        self.deleteButton.clicked.connect(self.delete_a_employee)

        # 输入数据区域
        self.Eid_lineEdit.setValidator(QRegExpValidator(QRegExp(id_reg.format(10))))
        self.Phonenumbers_lineEdit.setValidator(QRegExpValidator(QRegExp(phone_number_reg)))
        self.add_button.clicked.connect(self.add_a_employee)

        # 电话号码输入区域
        self.Phonenumbers_listWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不可修改
        self.Phonenumbers_add_pushButton.clicked.connect(self.add_a_phonenumber)
        self.lPhonenumbers_delete_pushButton.clicked.connect(self.delete_a_phonenumber)

        # 上下任
        self.manage_on_pushButton.clicked.connect(self.manage_on)
        self.manage_off_pushButton.clicked.connect(self.manage_off)

    def update_employee_Button(self):
        """
        修改员工按钮

        :return:
        """
        self.tableWidget.setEditTriggers(QAbstractItemView.AllEditTriggers)
        self.tableWidget.setItemDelegateForColumn(1, EmptyDelegate(self))  # 员工号不可修改
        self.tableWidget.setItemDelegateForColumn(4, EmptyDelegate(self))  # 年龄不可修改
        self.tableWidget.setItemDelegateForColumn(5, EmptyDelegate(self))  # 仓库号不可修改
        self.tableWidget.setItemDelegateForColumn(6, EmptyDelegate(self))  # 上任日期不可修改

    def manage_off(self):
        """
        卸任

        :return:
        """
        now_row = self.tableWidget.currentRow()
        if now_row == -1:
            self.errorBox.setText('请先选中员工再点击卸任')
            self.errorBox.exec_()
            return
        Eid = self.tableWidget.item(now_row, 1).text()
        Wid = self.tableWidget.item(now_row, 5).text()
        if Wid == 'None':
            self.errorBox.setText('该员工还没有上任，不需要卸任')
            self.errorBox.exec_()
            return
        try:
            utils.manages_add(self.cursor, Eid, Wid, None, datetime.datetime.now().strftime("%Y-%m-%d"))
        except warehouse_exception.ErrorWid:
            self.errorBox.setText('仓库号应该是4位！')
            self.errorBox.exec_()
            return
        except warehouse_exception.NotFindWid:
            self.errorBox.setText('找不到该仓库！')
            self.errorBox.exec_()
            return
        self.rightBox.setText('卸任成功！')
        self.rightBox.exec_()
        self.search_employee_by_name_Button()

    def manage_on(self):
        """
        上任

        :return:
        """
        now_row = self.tableWidget.currentRow()
        if now_row == -1:
            self.errorBox.setText('请先选中员工再点击上任')
            self.errorBox.exec_()
            return
        Wid = self.tableWidget.item(now_row, 5).text()
        if Wid != 'None':
            self.errorBox.setText('该员工目前正在管理该仓库，不能重复上任！')
            self.errorBox.exec_()
            return
        manage_on_inputDialog = QInputDialog()
        manage_on_inputDialog.setWindowTitle('上任')
        Wid, status = manage_on_inputDialog.getText(self, '上任', '请输入需要管理的仓库号（4位）')
        if not status:
            return
        Eid = self.tableWidget.item(now_row, 1).text()
        try:
            utils.manages_add(self.cursor, Eid, Wid, datetime.datetime.now().strftime("%Y-%m-%d"), None)
        except warehouse_exception.ErrorWid:
            self.errorBox.setText('仓库号应该是4位！')
            self.errorBox.exec_()
            return
        except warehouse_exception.NotFindWid:
            self.errorBox.setText('找不到该仓库！')
            self.errorBox.exec_()
            return
        self.rightBox.setText('上任成功！')
        self.rightBox.exec_()
        self.search_employee_by_name_Button()

    def delete_a_phonenumber(self):
        """
        删除一个电话号码

        :return:
        """
        for item in self.Phonenumbers_listWidget.selectedItems():
            self.Phonenumbers_listWidget.takeItem(self.Phonenumbers_listWidget.row(item))

    def add_a_phonenumber(self):
        """
        添加一个电话号码

        :return:
        """
        if len(self.Phonenumbers_lineEdit.text()) != 11:
            self.errorBox.setText('电话号码长度错误，应该为11位!')
            self.errorBox.exec_()
            return
        self.Phonenumbers_listWidget.addItem(QListWidgetItem(self.Phonenumbers_lineEdit.text()))

    def update_a_employee(self):
        """
        为updateButton绑定事件，修改一个员工

        :return:
        """
        table = 'employee_manage_view'
        try:
            row = self.tableWidget.currentItem().row()
        except AttributeError:
            return
        primary_key_value = ''
        changed_values = []
        phonenumbers = []
        types, _ = utils.get_types(self.cursor, table)
        for col in range(self.tableWidget.columnCount()):
            try:
                val = self.tableWidget.item(row, col).text()
            except IndexError:
                break
            except AttributeError:
                break
            if col <= 3:
                if types[col] == 'int':
                    val = int(val)
                if types[col].find('decimal') != -1:
                    val = float(val)
                if col == 1:
                    primary_key_value = val
                changed_values.append(val)
            elif col in [4, 5, 6]:
                continue
            else:
                if val != 'None':
                    phonenumbers.append(val)
        try:
            utils.employee_update(self.cursor, changed_values, phonenumbers, primary_key_value)
        except common_exception.DuplicatePhoneNumber as e:
            self.errorBox.setText("{}".format(e.value))
            self.errorBox.exec_()
            return
        except common_exception.ErrorPhoneNumber as e:
            self.errorBox.setText("{}".format(e.value))
            self.errorBox.exec_()
            return
        self.search_employee_by_name_Button()

    def delete_a_employee(self):
        """
        为deleteButton绑定事件，删除一个员工

        :return:
        """
        selected_items = self.tableWidget.selectedItems()
        for i in range(0, len(selected_items), self.tableWidget.columnCount()):
            try:
                utils.employee_delete(self.cursor, self.tableWidget.item(selected_items[i].row(), 1).text())
                self.tableWidget.removeRow(selected_items[i].row())
            except common_exception.DeleteError:
                self.errorBox.setText(
                    '不能删除员工[{}]！因为该员工在上任或者有记录！'.format(self.tableWidget.item(selected_items[i].row(), 1).text()))
                self.errorBox.exec_()
                continue

    def add_a_employee(self):
        """
        为add_button绑定事件，增加一个员工

        :return:
        """
        Ename = self.Ename_lineEdit.text()
        Eid = self.Eid_lineEdit.text()
        Esex = self.Esex_comboBox.currentText()
        Birthday = self.Birthday_dateEdit.date().toString(date_format)
        Phonenumbers = []
        for row in range(self.Phonenumbers_listWidget.count()):
            Phonenumbers.append(self.Phonenumbers_listWidget.item(row).text())
        try:
            utils.employee_add(self.cursor, Ename, Eid, Esex, Birthday, Phonenumbers)
        except employee_exception.DuplicateEid:
            self.errorBox.setText("员工号重复！")
            self.errorBox.exec_()
            return
        except employee_exception.ErrorEid:
            self.errorBox.setText("员工号长度错误，应该为10位！")
            self.errorBox.exec_()
            return
        except common_exception.DuplicatePhoneNumber as e:
            self.errorBox.setText("{}".format(e.value))
            self.errorBox.exec_()
            return
        self.tableWidget_start()
        self.rightBox.setText('添加员工成功！')
        self.rightBox.exec_()

    def tableWidget_start(self):
        """
        初始化员工表格数据

        :return:
        """
        self.tableWidget.clear()
        self.tableWidget.setRowCount(0)
        table = 'employee_manage_view'
        for result in utils.select_star_get_all(self.cursor, table):
            now_row = self.tableWidget.rowCount()
            self.tableWidget.setRowCount(now_row + 1)
            cur_col = 0
            Eid = ''
            for col, data in enumerate(result):
                if col == 1:
                    Eid = data
                self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(data)))
                cur_col += 1
            Phonenumbers = utils.select_star_get_more(self.cursor, 'phonenumbers', Eid)
            self.tableWidget.setColumnCount(max(self.tableWidget.columnCount(), cur_col + len(Phonenumbers)))
            if self.tableWidget.columnCount() == cur_col + len(Phonenumbers):
                names = ['员工姓名', '员工号', '性别', '出生日期', '年龄', '所管理仓库号', '上任日期']
                for i in range(len(Phonenumbers)):
                    names.append('电话号码{}'.format(i + 1))
                self.tableWidget.setHorizontalHeaderLabels(names)
            for Phonenumber in Phonenumbers:
                _, Phonenumber = Phonenumber
                self.tableWidget.setItem(now_row, cur_col, QTableWidgetItem('{}'.format(Phonenumber)))
                cur_col += 1

    def search_employee_by_name_Button(self):
        """
        根据员工名称搜索员工

        :return:
        """
        table = 'employee_manage_view'
        self.tableWidget.clearContents()
        self.tableWidget.setRowCount(0)
        to_search = self.search_lineEdit.text()
        for result in utils.select_star_get_by_name(self.cursor, table, to_search):
            now_row = self.tableWidget.rowCount()
            self.tableWidget.setRowCount(now_row + 1)
            cur_col = 0
            Eid = ''
            for col, data in enumerate(result):
                if col == 1:
                    Eid = data
                self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(data)))
                cur_col += 1
            Phonenumbers = utils.select_star_get_more(self.cursor, 'phonenumbers', Eid)
            self.tableWidget.setColumnCount(max(self.tableWidget.columnCount(), cur_col + len(Phonenumbers)))
            if self.tableWidget.columnCount() == cur_col + len(Phonenumbers):
                names = ['员工姓名', '员工号', '性别', '出生日期', '年龄', '所管理仓库号', '上任日期']
                for i in range(len(Phonenumbers)):
                    names.append('电话号码{}'.format(i + 1))
                self.tableWidget.setHorizontalHeaderLabels(names)
            for Phonenumber in Phonenumbers:
                _, Phonenumber = Phonenumber
                self.tableWidget.setItem(now_row, cur_col, QTableWidgetItem('{}'.format(Phonenumber)))
                cur_col += 1


class ShelvesShow(QMainWindow, shelves_ui.Ui_shelves):
    def __init__(self, parent=None):
        super(ShelvesShow, self).__init__(parent)
        self.setupUi(self)
        # 初始化&属性
        self.db, self.cursor = db, cursor
        self.errorBox = QMessageBox()
        self.errorBox.setIcon(QMessageBox.Critical)
        self.warningBox = QMessageBox()
        self.warningBox.setIcon(QMessageBox.Warning)
        self.rightBox = QMessageBox()
        self.rightBox.setIcon(QMessageBox.Information)

        # 表格
        self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不可修改
        self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)  # 选中一行
        self.tableWidget.setSelectionMode(QAbstractItemView.ExtendedSelection)  # 设置为可以选中多个目标
        self.tableWidget.horizontalHeader().sectionClicked.connect(self.tableWidget.selectAll)  # 点击表头为全选
        self.tableWidget.itemChanged.connect(self.update_a_shelves)  # 表格内容修改后，数据库也修改

        # 搜索框
        self.searchButton.clicked.connect(self.search_shelves_by_name_Button)

        # 修改删除区域
        self.updateButton.clicked.connect(self.update_shelves_Button)
        self.confirm_update_Button.clicked.connect(
            lambda: self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers))
        self.deleteButton.clicked.connect(self.delete_a_shelves)

        # 输入数据区域
        self.Sid_input.setValidator(QRegExpValidator(QRegExp(id_reg.format(4))))
        self.Snum_input.setValidator(QRegExpValidator(QRegExp(int_reg)))
        self.add_button.clicked.connect(self.add_a_shelves)

    def update_shelves_Button(self):
        """
        修改货架按钮

        :return:
        """
        self.tableWidget.setEditTriggers(QAbstractItemView.AllEditTriggers)
        self.tableWidget.setItemDelegateForColumn(1, EmptyDelegate(self))  # 仓库号不可修改

    def update_a_shelves(self):
        """
        为updateButton绑定事件，修改一个货架

        :return:
        """
        table = 'shelves'
        try:
            row = self.tableWidget.currentItem().row()
        except AttributeError:
            return
        primary_key_value = ''
        changed_values = []
        types, primary = utils.get_types(self.cursor, table)
        for col in range(self.tableWidget.columnCount()):
            val = self.tableWidget.item(row, col).text()
            if types[col] == 'int':
                val = int(val)
            if types[col].find('decimal') != -1:
                val = float(val)
            if col == primary[0]:
                primary_key_value = val
            changed_values.append(val)
        utils.update_star(self.cursor, table, changed_values, primary_key_value)

    def delete_a_shelves(self):
        """
        为deleteButton绑定事件，删除一个货架

        :return:
        """
        table = 'shelves'
        selected_items = self.tableWidget.selectedItems()
        for i in range(0, len(selected_items), self.tableWidget.columnCount()):
            try:
                utils.delete_star(self.cursor, table, self.tableWidget.item(selected_items[i].row(), 1).text())
                self.tableWidget.removeRow(selected_items[i].row())
            except common_exception.DeleteError:
                self.errorBox.setText(
                    '不能删除货架[{}]！因为该货架有商品或者有记录！'.format(self.tableWidget.item(selected_items[i].row(), 1).text()))
                self.errorBox.exec_()
                continue

    def add_a_shelves(self):
        """
        为add_a_shelves_button绑定事件，增加一个货架

        :return:
        """
        Sname = self.Sname_input.text()
        Sid = self.Sid_input.text()
        Sdistrict = self.Sdistrict_input.text()
        try:
            Snum = int(self.Snum_input.text())
        except ValueError:
            Snum = 'null'
        try:
            utils.shelves_add(self.cursor, Sname, Sid, Sdistrict, Snum)
        except shelves_exception.DuplicateSid:
            self.errorBox.setText("货架号重复！")
            self.errorBox.exec_()
            return
        except shelves_exception.ErrorSid:
            self.errorBox.setText("货架号长度错误，应该为4位！")
            self.errorBox.exec_()
            return
        result = [Sname, Sid, Sdistrict, Snum]
        now_row = self.tableWidget.rowCount()
        self.tableWidget.setRowCount(now_row + 1)
        for col, data in enumerate(result):
            self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(data)))
        self.rightBox.setText('添加货架成功！')
        self.rightBox.exec_()

    def tableWidget_start(self):
        """
        初始化货架表格数据

        :return:
        """
        self.tableWidget.clear()
        self.tableWidget.setRowCount(0)
        names = ['货架名', '货架号', '所在区域', '所在区域中的编号']
        self.tableWidget.setHorizontalHeaderLabels(names)
        table = 'shelves'
        for result in utils.select_star_get_all(self.cursor, table):
            now_row = self.tableWidget.rowCount()
            self.tableWidget.setRowCount(now_row + 1)
            for col, data in enumerate(result):
                self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(data)))

    def search_shelves_by_name_Button(self):
        """
        根据货架名称搜索仓库

        :return:
        """
        table = 'shelves'
        self.tableWidget.clearContents()
        self.tableWidget.setRowCount(0)
        Gname_to_search = self.search_lineEdit.text()
        for result in utils.select_star_get_by_name(self.cursor, table, Gname_to_search):
            now_row = self.tableWidget.rowCount()
            self.tableWidget.setRowCount(now_row + 1)
            for col, data in enumerate(result):
                self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(data)))


class WarehouseShow(QMainWindow, warehouse_ui.Ui_warehouse):
    def __init__(self, parent=None):
        super(WarehouseShow, self).__init__(parent)
        self.setupUi(self)
        # 初始化&属性
        self.db, self.cursor = db, cursor
        self.errorBox = QMessageBox()
        self.errorBox.setIcon(QMessageBox.Critical)
        self.warningBox = QMessageBox()
        self.warningBox.setIcon(QMessageBox.Warning)
        self.rightBox = QMessageBox()
        self.rightBox.setIcon(QMessageBox.Information)

        # 进货
        self.purchase_dialog = purchase_Dialog()
        self.purchase_dialog.add_purchase_button.clicked.connect(self.add_purchase)
        self.purchase_dialog.Gid_input.setValidator(QRegExpValidator(QRegExp(id_reg.format(10))))
        self.purchase_dialog.Quality_days_input.setValidator(QRegExpValidator(QRegExp(int_reg)))
        self.purchase_dialog.Sprice_lineEdit.setValidator(QRegExpValidator(QRegExp(pos_float_reg)))
        self.purchase_dialog.Wid_lineEdit.setValidator(QRegExpValidator(QRegExp(id_reg.format(4))))
        self.purchase_dialog.Su_phonenumber_lineEdit.setValidator(QRegExpValidator(QRegExp(phone_number_reg)))
        self.purchase_dialog.Purchase_price_lineEdit.setValidator(QRegExpValidator(QRegExp(pos_float_reg)))
        self.purchase_dialog.Pnumber_lineEdit.setValidator(QRegExpValidator(QRegExp(int_reg)))

        # 表格
        self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不可修改
        self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)  # 选中一行
        self.tableWidget.setSelectionMode(QAbstractItemView.ExtendedSelection)  # 设置为可以选中多个目标
        self.tableWidget.horizontalHeader().sectionClicked.connect(self.tableWidget.selectAll)  # 点击表头为全选
        self.tableWidget.itemChanged.connect(self.update_a_warehouse)  # 表格内容修改后，数据库也修改

        # 搜索框
        self.searchButton.clicked.connect(self.search_warehouse_by_name_Button)

        # 修改删除区域
        self.updateButton.clicked.connect(self.update_warehouse_Button)
        self.confirm_update_Button.clicked.connect(
            lambda: self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers))
        self.deleteButton.clicked.connect(self.delete_a_warehouse)

        # 输入数据区域
        self.Wid_input.setValidator(QRegExpValidator(QRegExp(id_reg.format(4))))
        self.Wnum_input.setValidator(QRegExpValidator(QRegExp(int_reg)))
        self.add_a_warehouse_button.clicked.connect(self.add_a_warehouse)

        # 右侧功能区
        self.goto_purchase.clicked.connect(self.purchase_dialog_start)

    def update_warehouse_Button(self):
        """
        修改仓库按钮

        :return:
        """
        self.tableWidget.setEditTriggers(QAbstractItemView.AllEditTriggers)
        self.tableWidget.setItemDelegateForColumn(1, EmptyDelegate(self))  # 仓库号不可修改

    def add_purchase(self):
        """
        为add_purchase_button绑定事件，进货

        :return:
        """
        Gname = self.purchase_dialog.Gname_input.text()
        Gid = self.purchase_dialog.Gid_input.text()
        Manufacture_date = self.purchase_dialog.Manufacture_date_dateEdit.date().toString(date_format)
        try:
            Quality_days = int(self.purchase_dialog.Quality_days_input.text())
        except ValueError:
            self.errorBox.setText('保质期不能为空！')
            self.errorBox.exec_()
            return
        Manufacturer = self.purchase_dialog.Manufacturer_input.text()
        Unit = self.purchase_dialog.Unit_comboBox.currentText()
        try:
            Sprice = float(self.purchase_dialog.Sprice_lineEdit.text())
        except ValueError:
            self.errorBox.setText('售价不能为空！')
            self.errorBox.exec_()
            return
        Wid = self.purchase_dialog.Wid_lineEdit.text()
        Su_name = self.purchase_dialog.Su_name_lineEdit.text()
        Su_sex = self.purchase_dialog.Su_sex_comboBox.currentText()
        Su_phonenumber = self.purchase_dialog.Su_phonenumber_lineEdit.text()
        Purchase_date = datetime.datetime.now().strftime('%Y-%m-%d')
        try:
            Purchase_price = float(self.purchase_dialog.Purchase_price_lineEdit.text())
        except ValueError:
            self.errorBox.setText('进价不能为空！')
            self.errorBox.exec_()
            return
        try:
            Pnumber = int(self.purchase_dialog.Pnumber_lineEdit.text())
        except ValueError:
            self.errorBox.setText('进货数目不能为空！')
            self.errorBox.exec_()
            return
        try:
            utils.purchases_add(self.cursor, Gname, Gid, Manufacture_date, Quality_days, Manufacturer, Unit, Sprice,
                                Wid,
                                Su_name, Su_sex, Su_phonenumber, Purchase_date, Purchase_price, Pnumber)
        except goods_exception.DuplicateGid:
            self.errorBox.setText('商品号重复！')
            self.errorBox.exec_()
            return
        except warehouse_exception.ErrorWid:
            self.errorBox.setText('找不到该仓库！')
            self.errorBox.exec_()
            return
        except common_exception.ErrorPhoneNumber:
            self.errorBox.setText('电话号码格式错误！应该有11位！')
            self.errorBox.exec_()
            return
        self.rightBox.setText('添加成功！')
        self.rightBox.exec_()

    def purchase_dialog_start(self):
        """
        进货界面

        :return:
        """
        self.purchase_dialog.show()

    def update_a_warehouse(self):
        """
        为updateButton绑定事件，修改一个仓库

        :return:
        """
        table = 'warehouse'
        try:
            row = self.tableWidget.currentItem().row()
        except AttributeError:
            return
        primary_key_value = ''
        changed_values = []
        types, primary = utils.get_types(self.cursor, table)
        for col in range(self.tableWidget.columnCount()):
            val = self.tableWidget.item(row, col).text()
            if types[col] == 'int':
                try:
                    val = int(val)
                except ValueError:
                    self.errorBox.setText('修改失败！仓库所在门牌号必须是数字！')
                    self.errorBox.exec_()
                    self.tableWidget_start()
                    return
            if types[col].find('decimal') != -1:
                val = float(val)
            if col == primary[0]:
                primary_key_value = val
            changed_values.append(val)
        utils.update_star(self.cursor, table, changed_values, primary_key_value)

    def delete_a_warehouse(self):
        """
        为deleteButton绑定事件，删除一个仓库

        :return:
        """
        table = 'warehouse'
        selected_items = self.tableWidget.selectedItems()
        for i in range(0, len(selected_items), self.tableWidget.columnCount()):
            try:
                utils.delete_star(self.cursor, table, self.tableWidget.item(selected_items[i].row(), 1).text())
                self.tableWidget.removeRow(selected_items[i].row())
            except common_exception.DeleteError:
                self.errorBox.setText(
                    '不能删除仓库[{}]！因为该仓库有库存或者有记录！'.format(self.tableWidget.item(selected_items[i].row(), 1).text()))
                self.errorBox.exec_()
                continue

    def add_a_warehouse(self):
        """
        为add_a_warehouse_button绑定事件，增加一个仓库

        :return:
        """
        Wname = self.Wname_input.text()
        Wid = self.Wid_input.text()
        Wdistrict = self.Wdistrict_input.text()
        Wstreet = self.Wstreet_input.text()
        try:
            Wnum = int(self.Wnum_input.text())
        except ValueError:
            Wnum = 'null'
        try:
            utils.warehouse_add(self.cursor, Wname, Wid, Wdistrict, Wstreet, Wnum)
        except warehouse_exception.DuplicateWid:
            self.errorBox.setText("仓库号重复!")
            self.errorBox.exec_()
            return
        except warehouse_exception.ErrorWid:
            self.errorBox.setText("仓库号长度错误，应该为4位!")
            self.errorBox.exec_()
            return
        result = [Wname, Wid, Wdistrict, Wstreet, Wnum]
        now_row = self.tableWidget.rowCount()
        self.tableWidget.setRowCount(now_row + 1)
        for col, data in enumerate(result):
            self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(data)))
        self.rightBox.setText("添加仓库成功!")
        self.rightBox.exec_()

    def tableWidget_start(self):
        """
        初始化仓库表格数据

        :return:
        """
        self.tableWidget.clear()
        self.tableWidget.setRowCount(0)
        names = ['仓库名', '仓库号', '仓库所在区', '仓库所在街道', '仓库所在门牌号']
        self.tableWidget.setHorizontalHeaderLabels(names)
        table = 'warehouse'
        for result in utils.select_star_get_all(self.cursor, table):
            now_row = self.tableWidget.rowCount()
            self.tableWidget.setRowCount(now_row + 1)
            for col, data in enumerate(result):
                self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(data)))

    def search_warehouse_by_name_Button(self):
        """
        根据仓库名称搜索仓库

        :return:
        """
        table = 'warehouse'
        self.tableWidget.clearContents()
        self.tableWidget.setRowCount(0)
        Gname_to_search = self.search_lineEdit.text()
        for result in utils.select_star_get_by_name(self.cursor, table, Gname_to_search):
            now_row = self.tableWidget.rowCount()
            self.tableWidget.setRowCount(now_row + 1)
            for col, data in enumerate(result):
                self.tableWidget.setItem(now_row, col, QTableWidgetItem('{}'.format(data)))


class SupermarketShow(QMainWindow, main_ui.Ui_supermarket):
    def __init__(self, parent=None):
        super(SupermarketShow, self).__init__(parent)
        self.setupUi(self)
        # 初始化&属性
        self.db, self.cursor = db, cursor
        self.errorBox = QMessageBox()
        self.errorBox.setIcon(QMessageBox.Critical)
        self.warningBox = QMessageBox()
        self.warningBox.setIcon(QMessageBox.Warning)
        self.rightBox = QMessageBox()
        self.rightBox.setIcon(QMessageBox.Information)
        self.sales_dict = {}

        # 历史进货记录
        self.purchase_history_dialog = purchase_history_Dialog()
        self.purchase_history_dialog.searchButton.clicked.connect(self.purchase_history_search)
        self.purchase_history_dialog.history_Widget.setEditTriggers(QAbstractItemView.NoEditTriggers)

        # 历史上下架记录
        self.on_off_shelve_history_dialog = on_off_shelve_history_Dialog()
        self.on_off_shelve_history_dialog.searchButton.clicked.connect(self.on_off_shelve_history_search)
        self.on_off_shelve_history_dialog.history_Widget.setEditTriggers(QAbstractItemView.NoEditTriggers)

        # 历史销售记录
        self.sale_history_dialog = sale_history_Dialog()
        self.sale_history_dialog.searchButton.clicked.connect(self.sale_history_search)
        self.sale_history_dialog.history_Widget.setEditTriggers(QAbstractItemView.NoEditTriggers)

        # 历史仓库管理员上下任记录
        self.manage_history_dialog = manage_history_Dialog()
        self.manage_history_dialog.searchButton.clicked.connect(self.manage_history_search)
        self.manage_history_dialog.history_Widget.setEditTriggers(QAbstractItemView.NoEditTriggers)

        # 统计历史营收
        self.sale_statistics_dialog = sale_statistics_Dialog()
        self.sale_statistics_dialog.sales_statistics_Widget.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.sale_statistics_dialog.setDateTimeButton.clicked.connect(self.sale_statistics_search)
        self.sale_statistics_dialog.searchButton.clicked.connect(self.search_sales_statistics_by_name_Button)

        # 结账界面
        self.check_out_dialog = check_out_Dialog()
        self.check_out_dialog.Pay_mode_comboBox.currentTextChanged.connect(self.is_cash)
        self.check_out_dialog.Eid_input.setValidator(QRegExpValidator(QRegExp(id_reg.format(10))))
        self.check_out_dialog.confirm_Box.accepted.connect(self.check_out)
        self.check_out_dialog.confirm_Box.rejected.connect(self.check_out_dialog.close)

        # 最上面一栏
        self.timer = QTimer()
        self.timer.start(1000)  # 每隔一秒刷新一次，这里设置为1000ms
        self.timer.timeout.connect(self.showtime)

        # 表格
        self.BillWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不可修改
        self.BillWidget.setSelectionBehavior(QAbstractItemView.SelectRows)  # 选中一行
        self.BillWidget.setSelectionMode(QAbstractItemView.ExtendedSelection)  # 设置为可以选中多个目标
        self.BillWidget.horizontalHeader().sectionClicked.connect(self.BillWidget_Header_click)
        self.deleteButton.clicked.connect(self.delete_a_sale)

        # 输入数据区域
        self.Gid_input.setValidator(QRegExpValidator(QRegExp(id_reg.format(10))))
        self.Srecord_num_input.setValidator(QRegExpValidator(QRegExp(int_reg)))
        self.add_a_sale_button.clicked.connect(self.add_a_sale)
        self.check_out_Button.clicked.connect(self.check_out_start)

        # 右侧统计数据
        self.goto_sale_statistics.clicked.connect(self.sale_statistics_start)
        self.goto_purchase_history.clicked.connect(self.purchase_history_start)
        self.goto_on_off_shelve_history.clicked.connect(self.on_off_shelve_history_start)
        self.goto_sale_history.clicked.connect(self.sale_history_start)
        self.goto_manage_history.clicked.connect(self.manage_history_start)

    def manage_history_start(self):
        """
        查询仓库管理员上下任历史记录的按钮的绑定函数

        :return:
        """
        table = 'manages'
        start_time = utils.get_earliest_date(self.cursor, table)
        end_time = QDateTime.currentDateTime()
        self.manage_history_dialog.start_dateEdit.setDateTime(start_time)
        self.manage_history_dialog.end_dateEdit.setDateTime(end_time)
        self.manage_history_search()
        self.manage_history_dialog.show()

    def manage_history_search(self):
        """
        展示仓库管理员上下任历史记录

        :return:
        """
        table = 'manages'
        start_date = self.manage_history_dialog.start_dateEdit.date()
        end_date = self.manage_history_dialog.end_dateEdit.date()
        self.manage_history_dialog.history_Widget.clearContents()
        self.manage_history_dialog.history_Widget.setRowCount(0)
        for result in utils.select_star_get_duration(self.cursor, table, start_date.toString('yyyy-MM-dd'),
                                                     end_date.toString('yyyy-MM-dd')):
            now_row = self.manage_history_dialog.history_Widget.rowCount()
            self.manage_history_dialog.history_Widget.setRowCount(now_row + 1)
            for col, data in enumerate(result):
                self.manage_history_dialog.history_Widget.setItem(now_row, col,
                                                                  QTableWidgetItem('{}'.format(data)))

    def sale_history_start(self):
        """
        查询销售历史记录的按钮的绑定函数

        :return:
        """
        table = 'sales'
        start_time = utils.get_earliest_date(self.cursor, table)
        end_time = QDateTime.currentDateTime()
        self.sale_history_dialog.start_dateEdit.setDateTime(start_time)
        self.sale_history_dialog.end_dateEdit.setDateTime(end_time)
        self.sale_history_search()
        self.sale_history_dialog.show()

    def sale_history_search(self):
        """
        展示销售历史记录

        :return:
        """
        table = 'sales'
        start_date = self.sale_history_dialog.start_dateEdit.date()
        end_date = self.sale_history_dialog.end_dateEdit.date()
        self.sale_history_dialog.history_Widget.clearContents()
        self.sale_history_dialog.history_Widget.setRowCount(0)
        for result in utils.select_star_get_duration(self.cursor, table, start_date.toString('yyyy-MM-dd'),
                                                     end_date.toString('yyyy-MM-dd')):
            now_row = self.sale_history_dialog.history_Widget.rowCount()
            self.sale_history_dialog.history_Widget.setRowCount(now_row + 1)
            for col, data in enumerate(result):
                self.sale_history_dialog.history_Widget.setItem(now_row, col,
                                                                QTableWidgetItem('{}'.format(data)))

    def on_off_shelve_history_start(self):
        """
        查询上下架历史记录的按钮的绑定函数

        :return:
        """
        table = 'launches_off'
        start_time = utils.get_earliest_date(self.cursor, table)
        end_time = QDateTime.currentDateTime()
        self.on_off_shelve_history_dialog.start_dateEdit.setDateTime(start_time)
        self.on_off_shelve_history_dialog.end_dateEdit.setDateTime(end_time)
        self.on_off_shelve_history_search()
        self.on_off_shelve_history_dialog.show()

    def on_off_shelve_history_search(self):
        """
        展示上下架历史记录

        :return:
        """
        if self.on_off_shelve_history_dialog.on_shleve_radioButton.isChecked():
            table = 'launches_off_on'
            self.on_off_shelve_history_dialog.history_Widget.setHorizontalHeaderLabels(
                ['上架记录号', '货架号', '商品号', '仓库号', '上架日期', '上架数目'])
        else:
            table = 'launches_off_off'
            self.on_off_shelve_history_dialog.history_Widget.setHorizontalHeaderLabels(
                ['下架记录号', '货架号', '商品号', '仓库号', '下架日期', '下架数目'])
        start_date = self.on_off_shelve_history_dialog.start_dateEdit.date()
        end_date = self.on_off_shelve_history_dialog.end_dateEdit.date()
        self.on_off_shelve_history_dialog.history_Widget.clearContents()
        self.on_off_shelve_history_dialog.history_Widget.setRowCount(0)
        for result in utils.select_star_get_duration(self.cursor, table, start_date.toString('yyyy-MM-dd'),
                                                     end_date.toString('yyyy-MM-dd')):
            now_row = self.on_off_shelve_history_dialog.history_Widget.rowCount()
            self.on_off_shelve_history_dialog.history_Widget.setRowCount(now_row + 1)
            index = 0
            for col, data in enumerate(result):
                if table == 'launches_off_on' and col == 5:
                    continue
                if table == 'launches_off_off' and col == 4:
                    continue
                if table == 'launches_off_off' and col == 6:
                    data = -data
                self.on_off_shelve_history_dialog.history_Widget.setItem(now_row, index,
                                                                         QTableWidgetItem('{}'.format(data)))
                index += 1

    def purchase_history_start(self):
        """
        查询进货历史记录的按钮的绑定函数

        :return:
        """
        table = 'purchase_history_view'
        start_time = utils.get_earliest_date(self.cursor, table)
        end_time = QDateTime.currentDateTime()
        self.purchase_history_dialog.start_dateEdit.setDateTime(start_time)
        self.purchase_history_dialog.end_dateEdit.setDateTime(end_time)
        self.purchase_history_search()
        self.purchase_history_dialog.show()

    def purchase_history_search(self):
        """
        展示进货历史记录

        :return:
        """
        table = 'purchase_history_view'
        start_date = self.purchase_history_dialog.start_dateEdit.date()
        end_date = self.purchase_history_dialog.end_dateEdit.date()
        self.purchase_history_dialog.history_Widget.clearContents()
        self.purchase_history_dialog.history_Widget.setRowCount(0)
        for result in utils.select_star_get_duration(self.cursor, table, start_date.toString('yyyy-MM-dd'),
                                                     end_date.toString('yyyy-MM-dd')):
            now_row = self.purchase_history_dialog.history_Widget.rowCount()
            self.purchase_history_dialog.history_Widget.setRowCount(now_row + 1)
            for col, data in enumerate(result):
                self.purchase_history_dialog.history_Widget.setItem(now_row, col,
                                                                    QTableWidgetItem('{}'.format(data)))

    def search_sales_statistics_by_name_Button(self):
        """
        根据名字查询销售统计信息

        :return:
        """
        start_time = self.sale_statistics_dialog.start_dateTimeEdit.text()
        end_time = self.sale_statistics_dialog.end_dateTimeEdit.text()
        self.sale_statistics_dialog.sales_statistics_Widget.clearContents()
        self.sale_statistics_dialog.sales_statistics_Widget.setRowCount(0)
        Gname_to_search = self.sale_statistics_dialog.searchEdit.text()
        for Gid, Gname, sales_total, profit, sales_num in utils.get_sales_info_by_name(self.cursor,
                                                                                       Gname_to_search,
                                                                                       start_time, end_time):
            now_row = self.sale_statistics_dialog.sales_statistics_Widget.rowCount()
            self.sale_statistics_dialog.sales_statistics_Widget.setRowCount(now_row + 1)
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 0, QTableWidgetItem('{}'.format(Gid)))
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 1,
                                                                        QTableWidgetItem('{}'.format(Gname)))
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 2,
                                                                        QTableWidgetItem('{}'.format(sales_total)))
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 3,
                                                                        QTableWidgetItem('{}'.format(profit)))
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 4,
                                                                        QTableWidgetItem('{}'.format(sales_num)))

    def sale_statistics_search(self):
        """
        查询销售营收的按钮：setDateTimeButton

        :return:
        """
        start_time = self.sale_statistics_dialog.start_dateTimeEdit.text()
        end_time = self.sale_statistics_dialog.end_dateTimeEdit.text()
        self.sale_statistics_dialog.sales_statistics_Widget.clearContents()
        self.sale_statistics_dialog.sales_statistics_Widget.setRowCount(0)
        sales_total_sum = 0
        profit_sum = 0
        sales_num_sum = 0
        for Gid, Gname, sales_total, profit, sales_num in utils.get_sales_info_all(self.cursor, start_time, end_time):
            now_row = self.sale_statistics_dialog.sales_statistics_Widget.rowCount()
            self.sale_statistics_dialog.sales_statistics_Widget.setRowCount(now_row + 1)
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 0, QTableWidgetItem('{}'.format(Gid)))
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 1,
                                                                        QTableWidgetItem('{}'.format(Gname)))
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 2,
                                                                        QTableWidgetItem('{}'.format(sales_total)))
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 3,
                                                                        QTableWidgetItem('{}'.format(profit)))
            self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 4,
                                                                        QTableWidgetItem('{}'.format(sales_num)))
            sales_total_sum += sales_total
            profit_sum += profit
            sales_num_sum += sales_num
        now_row = self.sale_statistics_dialog.sales_statistics_Widget.rowCount()
        self.sale_statistics_dialog.sales_statistics_Widget.setRowCount(now_row + 1)
        self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 0,
                                                                    QTableWidgetItem('总计'))
        self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 2,
                                                                    QTableWidgetItem('{}'.format(sales_total_sum)))
        self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 3,
                                                                    QTableWidgetItem('{}'.format(profit_sum)))
        self.sale_statistics_dialog.sales_statistics_Widget.setItem(now_row, 4,
                                                                    QTableWidgetItem('{}'.format(sales_num_sum)))
        self.sale_statistics_dialog.sales_statistics_Widget.setSpan(now_row, 0, 1, 2)

    def sale_statistics_start(self):
        """
        进入销售营收统计界面的按钮

        :return:
        """
        start_time = utils.get_earliest_date(self.cursor, table='purchases')
        end_time = QDateTime.currentDateTime()
        self.sale_statistics_dialog.start_dateTimeEdit.setDateTime(start_time)
        self.sale_statistics_dialog.end_dateTimeEdit.setDateTime(end_time)
        self.sale_statistics_search()
        self.sale_statistics_dialog.show()

    def check_out_start(self):
        """
        进入结账界面的按钮

        :return:
        """
        if self.BillWidget.rowCount() == 0:
            self.warningBox.setText('还没有添加物品，不需要结账！')
            self.warningBox.exec_()
        else:
            self.check_out_dialog.show()

    def check_out(self):
        """
        结账

        :return:
        """
        Pay_mode = self.check_out_dialog.Pay_mode_comboBox.currentText()
        Pay_account = self.check_out_dialog.Pay_account_input.text()
        Eid = self.check_out_dialog.Eid_input.text()
        check_out_time = QDateTime.currentDateTime().toString(datetime_format)
        check_out_Gids = []
        utils.start_transaction(self.cursor)
        for row in range(self.BillWidget.rowCount()):
            Gid = self.BillWidget.item(row, 2).text()
            check_out_Gids.append(Gid)
            Srecord_num = int(self.BillWidget.item(row, 5).text())
            goods_info = utils.select_star_get(self.cursor, 'goods', Gid)
            try:
                utils.sales_add(self.cursor, Gid, Eid, check_out_time,
                                Srecord_num, goods_info['Sprice'],
                                Pay_account, Pay_mode)
            except employee_exception.NotFindEid:
                utils.rollback_transaction(self.cursor)
                self.errorBox.setText('找不到该员工!')
                self.errorBox.exec_()
                return
        utils.commit_transaction(self.cursor)
        self.check_out_dialog.close()
        self.clear_bill()
        self.rightBox.setText('结账成功!')
        self.rightBox.exec_()
        Gid_set = set()
        for Gid in check_out_Gids:
            if Gid in Gid_set:
                continue
            Gid_set.add(Gid)
            Sid, left, latest_launch_date = utils.get_goods_left_shelves(self.cursor, Gid)
            if left == 0:
                flag = True
                warehouse_left = []
                for Wid, num in utils.get_goods_left_warehouse_all(self.cursor, Gid):
                    if num != 0:
                        flag = False
                        warehouse_left.append(Wid)
                if not flag:
                    self.warningBox.setText('在货架[{}]上的商品[{}]已卖完，仓库{}中还有库存，请即时上架！'.format(Sid, Gid, warehouse_left))
                    self.warningBox.exec_()
                else:
                    self.warningBox.setText('在货架[{}]上的商品[{}]已卖完，并且仓库中也没有库存，请即时进货！'.format(Sid, Gid))
                    self.warningBox.exec_()

    def clear_bill(self):
        """
        结账之后清空账单

        :return:
        """
        self.sales_dict = {}
        self.BillWidget.selectAll()
        selected_items = self.BillWidget.selectedItems()
        for i in range(0, len(selected_items), 6):
            self.BillWidget.removeRow(selected_items[i].row())
        self.Gid_input.setText('')
        self.Srecord_num_input.setText('')
        self.BillWidget.clear()
        self.BillWidget.setRowCount(0)

    def delete_a_sale(self):
        """
        删除销售记录

        :return:
        """
        selected_items = self.BillWidget.selectedItems()
        for i in range(0, len(selected_items), 6):
            self.sales_dict[self.BillWidget.item(selected_items[i].row(), 2).text()] += int(
                self.BillWidget.item(selected_items[i].row(), 5).text())
            self.BillWidget.removeRow(selected_items[i].row())
        for i in range(self.BillWidget.rowCount()):
            self.BillWidget.setItem(i, 1, QTableWidgetItem('{}'.format(i + 1)))

    def BillWidget_Header_click(self):
        """
        全选所有表中数据

        :return:
        """
        self.BillWidget.selectAll()

    def showtime(self):
        """
        获取当前时间

        :return:
        """
        time = QDateTime.currentDateTime()  # 获取当前时间
        time_display = time.toString(datetime_format_extra)  # 格式化一下时间
        self.now_time_label.setText(time_display)

    def is_cash(self):
        """
        在Pay_mode_comboBox（支付方式）选择cash时，将Pay_account_input（支付账号）置为-1，且不可修改

        :return:
        """
        if self.check_out_dialog.Pay_mode_comboBox.currentText() == 'Cash':
            self.check_out_dialog.Pay_account_input.clear()
            self.check_out_dialog.Pay_account_input.setText('-1')
            self.check_out_dialog.Pay_account_input.setEnabled(False)
        else:
            self.check_out_dialog.Pay_account_input.clear()
            self.check_out_dialog.Pay_account_input.setEnabled(True)

    def add_a_sale(self):
        """
        为add_a_sale_button绑定事件，增加一条购买记录

        :return:
        """
        Gid = self.Gid_input.text()
        try:
            Srecord_num = int(self.Srecord_num_input.text())
        except ValueError:
            self.errorBox.setText('请输入销售数目!')
            self.errorBox.exec_()
            return
        try:
            goods_info = utils.select_star_get(self.cursor, 'goods', Gid)
        except TypeError:
            self.errorBox.setText('找不到该商品号!')
            self.errorBox.exec_()
            return
        if Gid in self.sales_dict:
            left_num = self.sales_dict[Gid]
        else:
            Sid, left_num, _ = utils.get_goods_left_shelves(self.cursor, Gid)
            self.sales_dict[Gid] = left_num
        if Srecord_num > left_num:
            self.errorBox.setText('售出的比货架上的还多！出现错误！')
            self.errorBox.exec_()
            return
        self.sales_dict[Gid] -= Srecord_num
        now_row = self.BillWidget.rowCount()
        self.BillWidget.setRowCount(self.BillWidget.rowCount() + 1)
        self.BillWidget.setItem(now_row, 1, QTableWidgetItem('{}'.format(now_row + 1)))
        self.BillWidget.setItem(now_row, 2, QTableWidgetItem(Gid))
        self.BillWidget.setItem(now_row, 3, QTableWidgetItem(goods_info['Gname']))
        self.BillWidget.setItem(now_row, 4, QTableWidgetItem('{}'.format(goods_info['Sprice'])))
        self.BillWidget.setItem(now_row, 5, QTableWidgetItem('{}'.format(Srecord_num)))
        self.BillWidget.setItem(now_row, 6,
                                QTableWidgetItem('{}'.format(round(float(goods_info['Sprice']) * Srecord_num, 2))))

    def __del__(self):
        self.db.close()


def run():
    initialization.check()
    app = QApplication(sys.argv)
    controller = Controller()  # 控制器实例
    controller.show_supermarket()  # 默认展示的是超市页面
    sys.exit(app.exec_())


if __name__ == '__main__':
    # 初始化数据库
    initialization.create_tables()
    initialization.insert_data()
    initialization.create_view()
    db, cursor = initialization.create_index()
    initialization.check()
    # 界面启动
    run()
